package com.lovo.emp.dao.impl;

import com.lovo.common.dao.BaseDao;
import com.lovo.common.model.CutPageBean;
import com.lovo.common.model.EmpBean;
import com.lovo.emp.dao.IEmpDao;

import java.sql.SQLException;
import java.time.LocalDate;

/**
 * 员工管理dao实现类
 * @author 白德柯
 *
 */
public class EmpDaoImpl extends BaseDao implements IEmpDao {
    @Override
    public CutPageBean<EmpBean> findByItem(int page, EmpBean empBean) {
        String sql = "SELECT e.*,d.`dept_name`,w.`work_name` FROM emp e \n" +
                "LEFT JOIN dept d ON e.`dept_id`=d.`id`\n" +
                "LEFT JOIN work_info w ON e.`work_id`=w.`id`";
        String conditionSql = " where 1=1 ";
        if(empBean.getName() != null && !empBean.getName().equals("")){
            conditionSql += "AND e.`emp_name` LIKE '%"+empBean.getName()+"%' ";
        }
        if(empBean.getEducation() != null && !empBean.getEducation().equals("")){
            conditionSql += "AND e.`education` LIKE '%"+empBean.getEducation()+"%' ";
        }
        if(empBean.getDeptName() != null && !empBean.getDeptName().equals("")){
            conditionSql += "AND d.`dept_name` LIKE '%"+empBean.getDeptName()+"%' ";
        }
        if(empBean.getWorkName() != null && !empBean.getWorkName().equals("")){
            conditionSql += "AND w.`work_name` LIKE '%"+empBean.getWorkName()+"%' ";
        }
        CutPageBean<EmpBean> cutPageBean = new CutPageBean<>();
        this.setConnection();
        try {
            ps = con.prepareStatement(sql + conditionSql + CutPageBean.getPageSql(page));
            rs = ps.executeQuery();
            while(rs.next()){
                cutPageBean.getList().add(fill());
            }
            ps = con.prepareStatement("SELECT COUNT(*) cnt FROM ("+ sql + conditionSql +") t");
            rs = ps.executeQuery();
            rs.next();
            // 获取员工总数并设置给分页对象
            cutPageBean.setCount(rs.getInt("cnt"));
            System.out.println(cutPageBean);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return cutPageBean;
    }

    @Override
    public EmpBean findById(int id) {
        String sql = "SELECT e.*,d.`dept_name`,w.`work_name` FROM emp e \n" +
                "LEFT JOIN dept d ON e.`dept_id`=d.`id`\n" +
                "LEFT JOIN work_info w ON e.`work_id`=w.`id` " +
                "WHERE e.id=?";
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            rs.next();
            return fill();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return null;
    }

    @Override
    public int add(EmpBean empBean) {
        String sql = "INSERT INTO `crm`.`emp` (\n" +
                "  `dept_id`,\n" +
                "  `work_id`,\n" +
                "  `emp_name`,\n" +
                "  `gender`,\n" +
                "  `login_name`,\n" +
                "  `pwd`,\n" +
                "  `education`,\n" +
                "  `born`,\n" +
                "  `phone`,\n" +
                "  `subject`,\n" +
                "  `political`,\n" +
                "  `addr`,\n" +
                "  `join_date`,\n" +
                "  `face`\n" +
                ") \n" +
                "VALUES\n" +
                "  (\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?,\n" +
                "    ?\n" +
                "  )";
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setObject(1,empBean.getDeptId());
            ps.setObject(2,empBean.getWorkId());
            ps.setObject(3,empBean.getName());
            ps.setObject(4,empBean.getGender());
            ps.setObject(5,empBean.getLoginName());
            ps.setObject(6,"123456");
            ps.setObject(7,empBean.getEducation());
            ps.setObject(8,empBean.getBorn());
            ps.setObject(9,empBean.getPhone());
            ps.setObject(10,empBean.getSubject());
            ps.setObject(11,empBean.getPolitical());
            ps.setObject(12,empBean.getAddr());
            ps.setObject(13,empBean.getJoinDate());
            ps.setObject(14,empBean.getFace());
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        String sql = "DELETE FROM emp WHERE id=?";
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setObject(1,id);
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return 0;
    }

    @Override
    public int update(EmpBean empBean) {
        String sql = "UPDATE emp SET phone=?,political=?,dept_id=?,work_id=?\n" +
                        "WHERE id=?";
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setObject(1,empBean.getPhone());
            ps.setObject(2,empBean.getPolitical());
            ps.setObject(3,empBean.getDeptId());
            ps.setObject(4,empBean.getWorkId());
            ps.setObject(5,empBean.getId());
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally{
            this.close();
        }
        return 0;
    }

    @Override
    public EmpBean findByLoginNameAndPwd(EmpBean empBean) {
        String sql = "select * from emp where login_name=? and pwd=?";
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setObject(1,empBean.getLoginName());
            ps.setObject(2,empBean.getPwd());
            rs = ps.executeQuery();
            if(rs.next()){
                return fill();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return null;
    }

    private EmpBean fill() throws SQLException {
        EmpBean empBean = new EmpBean();
        empBean.setId(rs.getInt("id"));
        empBean.setDeptId(rs.getInt("dept_id"));
        if(isColumnExist("dept_name")){
            empBean.setDeptName(rs.getString("dept_name"));
        }

        empBean.setWorkId(rs.getInt("work_id"));
        if(isColumnExist("work_name")){
            empBean.setWorkName(rs.getString("work_name"));
        }

        empBean.setName(rs.getString("emp_name"));
        empBean.setGender(rs.getString("gender"));
        empBean.setLoginName(rs.getString("login_name"));
        empBean.setEducation(rs.getString("education"));
        if(rs.getDate("born") != null){
            empBean.setBorn(rs.getDate("born").toLocalDate());
        }
        empBean.setPhone(rs.getString("phone"));
        empBean.setSubject(rs.getString("subject"));
        empBean.setPolitical(rs.getString("political"));
        empBean.setAddr(rs.getString("addr"));
        if(rs.getDate("join_date") != null){
            empBean.setJoinDate(rs.getDate("join_date").toLocalDate());
        }
        empBean.setFace(rs.getString("face"));
        return empBean;
    }

    public static void main(String[] args) {
        EmpDaoImpl empDao = new EmpDaoImpl();
        EmpBean empBean = new EmpBean();
//        empBean.setId(3);
//        empBean.setPhone("13888888888");
//        empBean.setPolitical("党员");
//        empBean.setDeptId(1);
//        empBean.setWorkId(1);
//        empBean.setName("关羽");
//        empBean.setEducation("大专");
//        empBean.setDeptId(1);
//        empBean.setWorkId(1);
//        empBean.setBorn(LocalDate.parse("1998-10-01"));
//        empDao.findByItem(1,empBean);

//        System.out.println(empDao.add(empBean));

//        System.out.println(empDao.delete(28));
//        System.out.println(empDao.findById(1));
//        System.out.println(empDao.update(empBean));

        empBean.setLoginName("admin");
        empBean.setPwd("4123");
        System.out.println(empDao.findByLoginNameAndPwd(empBean));
    }
}
